package Shop;

import JDBC.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShopDaoImp implements ShopService {
    private static final int PAGE_SIZE = 12; // 每页显示的记录数
    @Override
    public List<Shop> pageshop(int page, String type) {
        List<Shop> shops = new ArrayList<>();

        Connection conn = DBHelper.getConn();
        int offset = (page - 1) * PAGE_SIZE;
// String sql="select * from User WHERE id='"+id+"';";
        String sql = "select * from shop WHERE manage=? LIMIT ?,?;";//通过管理员manage进行商品的查询
        PreparedStatement stat = null;
        ResultSet rs = null;
//Shop s=new Shop();

        ArrayList<Shop> shopList = new ArrayList<>();//创建arrayList集合

//Manage m=new Manage();
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, type);
            stat.setInt(2, offset);
            stat.setInt(3, PAGE_SIZE);
            rs = stat.executeQuery();//executeQuery该方法用来执行查询语句
            while (rs.next()) {

// m.setMname(rs.getString("mname"));
// m.setPassword(rs.getString("password"));
// m.setMphone(rs.getString("mphone"));
// m.setStorename(rs.getString("storename"));
                Shop s = new Shop();
                s.setProduct(rs.getString("product"));//获取商品名字
                s.setUrl(rs.getString("url"));//获取商品图片地址url
                s.setType(rs.getString("type"));//获取商品的类型
                s.setPrice(rs.getFloat("price"));//获取商品价格
                s.setQuantity(rs.getInt("quantity"));//获取商品数量
                s.setManage(rs.getString("manage"));//获取商品管理者名字

                System.out.println(s.getProduct());
                System.out.println(s.getUrl());
                System.out.println(s.getQuantity());
                shopList.add(s);//将这个商品添加到ShopList集合中，方便前端到时候进行打印遍历出来
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
// PreparedStatement stat = null;
            DBHelper.closeAll(conn, stat, rs);
        }
        System.out.println("查询结束");
        return shopList;

    }
    @Override
    public int count(String type) {

        Connection conn = DBHelper.getConn();
        String sql = "SELECT COUNT(*) FROM shop where manage=?";
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, type);
            rs = stat.executeQuery();

// 获取查询结果
            int databaseCount = 0;
            if (rs.next()) {
                databaseCount = rs.getInt(1);
            }
            System.out.println("总页数为:" + databaseCount);
            return databaseCount;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }

        System.out.println("总页数为0");
        return 0;
    }

    @Override
    public ArrayList<Shop> findShop(String manage) {
        System.out.println("hhh");
        Connection conn= DBHelper.getConn();
        String sql="select * from shop WHERE manage=?;";//通过管理员manage进行商品的查询
        PreparedStatement stat=null;
        ResultSet rs=null;
        //Shop.Shop s=new Shop.Shop();

        ArrayList<Shop> shopList = new ArrayList<>();//创建arrayList集合

        //Manage m=new Manage();
        try {
            stat=conn.prepareStatement(sql);
            stat.setString(1,manage);
            rs=stat.executeQuery();//executeQuery该方法用来执行查询语句
            while(rs.next()){

//                m.setMname(rs.getString("mname"));
//                m.setPassword(rs.getString("password"));
//                m.setMphone(rs.getString("mphone"));
//                m.setStorename(rs.getString("storename"));
                Shop s=new Shop();
                s.setProduct(rs.getString("product"));//获取商品名字
                s.setUrl(rs.getString("url"));//获取商品图片地址url
                s.setType(rs.getString("type"));//获取商品的类型
                s.setPrice(rs.getFloat("price"));//获取商品价格
                s.setQuantity(rs.getInt("quantity"));//获取商品数量
                s.setManage(rs.getString("manage"));//获取商品管理者名字

                System.out.println(s.getProduct());
                System.out.println(s.getUrl());
                System.out.println(s.getQuantity());
                shopList.add(s);//将这个商品添加到ShopList集合中，方便前端到时候进行打印遍历出来
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //       PreparedStatement stat = null;
            DBHelper.closeAll(conn,stat,rs);
        }
        System.out.println("查询结束");
        return shopList;
    }

    @Override
    public void createshop(String product, String url,String type, int quantity, float price,String manage) {

        Connection conn= DBHelper.getConn();
        String sql="INSERT shop(product,url,type,quantity,price,manage) VALUE (?,?,?,?,?,?);";
        PreparedStatement stat=null;
        ResultSet rs=null;
        try{

            stat=conn.prepareStatement(sql);
            stat.setString(1, product);
            stat.setString(2, url);
            stat.setString(3, type);
            stat.setInt(4, quantity);
            stat.setFloat(5, price);
            stat.setString(6,manage);//插入管理员

            stat.executeUpdate();
            System.out.println("创建成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.closeAll(conn,stat,rs);
        }
    }
    @Override
    public void deleteshop(String product) {

        Connection conn = DBHelper.getConn();
        String sql = "delete from shop WHERE product=?";
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, product);
            stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
//        }
//    }
        }

    }


    @Override
    public int allcount() {

        Connection conn = DBHelper.getConn();
        String sql = "SELECT COUNT(*) FROM shop ";
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
// 获取查询结果
            int databaseCount = 0;
            if (rs.next()) {
                databaseCount = rs.getInt(1);
            }
            System.out.println("总个数为:" + databaseCount);
            return databaseCount;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }

        System.out.println("总个数为0");
        return 0;
    }

    @Override
    public List<Shop> allpageshop(int page) {
        List<Shop> shops = new ArrayList<>();

        Connection conn = DBHelper.getConn();
        int offset = (page - 1) * PAGE_SIZE;
// String sql="select * from User WHERE id='"+id+"';";
        String sql = "select * from shop LIMIT ?,?;";//通过管理员manage进行商品的查询
        PreparedStatement stat = null;
        ResultSet rs = null;
//Shop s=new Shop();

        ArrayList<Shop> shopList = new ArrayList<>();//创建arrayList集合

//Manage m=new Manage();
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1, offset);
            stat.setInt(2, PAGE_SIZE);
            rs = stat.executeQuery();//executeQuery该方法用来执行查询语句
            while (rs.next()) {
                Shop s = new Shop();
                s.setProduct(rs.getString("product"));//获取商品名字
                s.setUrl(rs.getString("url"));//获取商品图片地址url
                s.setType(rs.getString("type"));//获取商品的类型
                s.setPrice(rs.getFloat("price"));//获取商品价格
                s.setQuantity(rs.getInt("quantity"));//获取商品数量
                s.setManage(rs.getString("manage"));//获取商品管理者名字

                System.out.println(s.getProduct());
                System.out.println(s.getUrl());
                System.out.println(s.getQuantity());
                shopList.add(s);//将这个商品添加到ShopList集合中，方便前端到时候进行打印遍历出来
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
// PreparedStatement stat = null;
            DBHelper.closeAll(conn, stat, rs);
        }
        System.out.println("查询结束");
        return shopList;

    }

}
